/***********************************************************************************************************************************
 * Name:	Logins_List.sql
 * Author:	Frank Figearo (frank.figearo@us.bbaaviation.com|frank@sqlnerd.me)
 * Summary:	Reports a server's logins along with any server-level role membership.
 */
SELECT
	[Server]			= @@SERVERNAME,
	[Login Name]		= m.name,
	[Login Type]		= m.type_desc,
	[Login Disabled]	= m.is_disabled,
	[Server Role]		= r.name,
	[Default Database]	= m.default_database_name
  FROM sys.server_principals r
	INNER JOIN sys.server_role_members rm ON (r.principal_id = rm.role_principal_id)
	RIGHT JOIN sys.server_principals m ON (rm. member_principal_id = m.principal_id)
  WHERE m.[type] <> 'R' AND r.name IS NOT Null
	AND m.name NOT IN ('sa', 'NT SERVICE\MSSQLSERVER', 'NT SERVICE\SQLSERVERAGENT', 'NT SERVICE\SQLWriter', 'NT SERVICE\Winmgmt', 'NT AUTHORITY\SYSTEM', 'BBAAviation\BBAUSASQLServerAdmins')
  ORDER BY [Login Type], [Login Name], [Server Role];
GO